CREATE procedure [dbo].[asi_UpdateContactSalutations]
    (@inputSalutationKey uniqueidentifier)
as
begin

set nocount on

/* create table tempFormula
    (tempKey uniqueidentifier,
    tempValue nvarchar(1000) )  */

declare @sql nvarchar(2000)
declare @contactKey uniqueidentifier
declare @IndividualFormula nvarchar(2000)
declare @InstituteFormula nvarchar(2000)

declare @calcFormula nvarchar(2000)

declare @tempKey uniqueidentifier
declare @tempValue nvarchar(1000)

select @IndividualFormula = IndividualFormula,
    @InstituteFormula = InstituteFormula
    from vBoSalutationRef where SalutationKey = @inputSalutationKey

declare theCursor cursor for
    select ContactKey
        from vBoContactSalutation
        where SalutationKey = @inputSalutationKey
            and IsOverridden = 0

open theCursor
fetch next from theCursor into @contactKey

WHILE @@FETCH_STATUS = 0
begin

set @tempKey = newid()

select @sql =  
    case
    when IsInstitute = 1 then
            ' asi_ProcessContactFormulaOutput '
                + '''' + convert (nvarchar(100), @tempKey) + ''', '
                + '''' + @InstituteFormula + ''', '
                + '''vBoInstitute'', ''ContactKey'', '
                + '''' + convert (nvarchar(50), @contactKey)
                + ''''

    when IsInstitute = 0 then
            ' asi_ProcessContactFormulaOutput '
                + '''' + convert (nvarchar(100), @tempKey) + ''', '
                + '''' + @IndividualFormula + ''', '
                + '''vBoIndividual'', ''ContactKey'', '
                + '''' + convert (nvarchar(50), @contactKey)
                + ''''
    end
from vBoContact where ContactKey = @contactKey

exec (@sql)

select @tempValue = tempValue from tempFormula
    where tempKey = @tempKey

if datalength (@tempValue) > 0 and @tempValue is not null
    update vBoContactSalutation set
        SalutationText = @tempValue
    where ContactKey = @contactKey and SalutationKey = @inputSalutationKey

delete from tempFormula where tempKey = @tempKey

fetch next from theCursor into @contactKey
end

close theCursor
deallocate theCursor

set nocount off

end

GO